Record displays in multi-table queries
There are two defaults that determine how records are displayed in queries that contain two or more tables:
The following sections explain these defaults.
Sort order default
When you create a query that contains more than one table, Results uses the primary index for the first table you included in the query to determine the order in which Results retrieves and displays the records. See Chapter 1, "Results Overview," for more information about indexes.
You can change the order in which records are sorted by selecting one or more fields to control the sort order for the query. For more information, see Chapter 5, " Changing the Query Selection Criteria."
Relationship types default
When you create a query that specifies a field that is contained in more than one table, by default Results retrieves only the records that contain data in the related fields.
Figure 3–1 helps to illustrate this relationship.
In Figure 3–1, notice that the Order table doesn’t contain any orders for Customer 5. Thus, a query to display the orders for each customer does not display a record for customer. This type of a relationship is called a partial relationship, or inner join.
The sample query identified in Table 3–2 illustrates the results of a partial relationship between the Customer and Order tables.
|
Cust-num
|
Name
|
Order-num
|
|---|---|---|
|
1
|
Lift Line Skiing
|
6
|
|
2
|
Urpon Frisbee
|
94
|
|
3
|
Hoops Croquet Co.
|
55
|
|
4
|
Go Fishing Ltd.
|
21
|
|
6
|
Fanatical Athletes
|
135
|
If you want to display all the records in the Customer table whether or not the Order table contains a related record, you can change the type of relationship between the tables to a complete relationship, or an outer join. The sample query identified in Table 3–3 illustrates the results of a complete relationship between the Customer and Order tables.
For details on how to change table relationships in multi-table queries, see Chapter 5, " Changing the Query Selection Criteria."